/*
 * ProductDao.java
 * 作用：数据访问对象（DAO），负责与数据库中的 `products` 表进行交互，提供商品的增删改查操作。
 */
package com.campustradingwall.dao;

import com.campustradingwall.model.Product;
import com.campustradingwall.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {

    /**
     * 分页获取所有上架的商品
     * @param keyword 搜索关键词，可为空
     * @param offset 起始位置
     * @param limit 每页数量
     * @return 商品列表
     */
    public List<Product> getAllOnShelfProducts(String keyword, int offset, int limit) {
        List<Product> products = new ArrayList<>();
        StringBuilder sqlBuilder = new StringBuilder("SELECT p.*, u.username as seller_name FROM products p JOIN users u ON p.user_id = u.id WHERE p.status = 'on_shelf'");
        
        if (keyword != null && !keyword.trim().isEmpty()) {
            sqlBuilder.append(" AND (p.name LIKE ? OR p.description LIKE ?)");
        }
        sqlBuilder.append(" ORDER BY p.created_at DESC LIMIT ? OFFSET ?");
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {
            int paramIndex = 1;
            if (keyword != null && !keyword.trim().isEmpty()) {
                stmt.setString(paramIndex++, "%" + keyword + "%");
                stmt.setString(paramIndex++, "%" + keyword + "%");
            }
            stmt.setInt(paramIndex++, limit);
            stmt.setInt(paramIndex++, offset);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product product = extractProductFromResultSet(rs);
                    com.campustradingwall.model.User seller = new com.campustradingwall.model.User();
                    seller.setUsername(rs.getString("seller_name"));
                    product.setSeller(seller);
                    products.add(product);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }
    
    /**
     * 获取所有商品（管理员用）
     * @return 商品列表
     */
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.*, u.username as seller_name FROM products p JOIN users u ON p.user_id = u.id ORDER BY p.created_at DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Product product = extractProductFromResultSet(rs);
                com.campustradingwall.model.User seller = new com.campustradingwall.model.User();
                seller.setUsername(rs.getString("seller_name"));
                product.setSeller(seller);
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }


    /**
     * 获取上架商品的总数
     * @param keyword 搜索关键词，可为空
     * @return 总数
     */
    public int getOnShelfProductCount(String keyword) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT COUNT(*) FROM products WHERE status = 'on_shelf'");
        if (keyword != null && !keyword.trim().isEmpty()) {
            sqlBuilder.append(" AND (name LIKE ? OR description LIKE ?)");
        }
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {
            int paramIndex = 1;
            if (keyword != null && !keyword.trim().isEmpty()) {
                stmt.setString(paramIndex++, "%" + keyword + "%");
                stmt.setString(paramIndex++, "%" + keyword + "%");
            }
            
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据ID获取商品详情
     * @param productId 商品ID
     * @return 商品对象
     */
    public Product getProductById(int productId) {
        String sql = "SELECT p.*, u.username as seller_name, u.phone as seller_phone FROM products p JOIN users u ON p.user_id = u.id WHERE p.id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, productId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Product product = extractProductFromResultSet(rs);
                    com.campustradingwall.model.User seller = new com.campustradingwall.model.User();
                    seller.setId(rs.getInt("user_id"));
                    seller.setUsername(rs.getString("seller_name"));
                    seller.setPhone(rs.getString("seller_phone"));
                    product.setSeller(seller);
                    return product;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
     /**
     * 根据用户ID获取其发布的所有商品
     * @param userId 用户ID
     * @return 商品列表
     */
    public List<Product> getProductsByUserId(int userId) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT * FROM products WHERE user_id = ? ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    products.add(extractProductFromResultSet(rs));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }


    /**
     * 添加新商品
     * @param product 商品对象
     */
    public void addProduct(Product product) {
        String sql = "INSERT INTO products (user_id, name, description, price, image_url, status) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, product.getUserId());
            stmt.setString(2, product.getName());
            stmt.setString(3, product.getDescription());
            stmt.setBigDecimal(4, product.getPrice());
            stmt.setString(5, product.getImageUrl());
            stmt.setString(6, product.getStatus());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除商品
     * @param productId 商品ID
     */
    public void deleteProduct(int productId) {
        String sql = "DELETE FROM products WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, productId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新商品信息
     * @param product 商品对象
     * @return true 如果更新成功，否则 false
     */
    public boolean updateProduct(Product product) {
        String sql = "UPDATE products SET name = ?, description = ?, price = ?, image_url = ?, status = ? WHERE id = ? AND user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, product.getName());
            stmt.setString(2, product.getDescription());
            stmt.setBigDecimal(3, product.getPrice());
            stmt.setString(4, product.getImageUrl());
            stmt.setString(5, product.getStatus());
            stmt.setInt(6, product.getId());
            stmt.setInt(7, product.getUserId());
            int affectedRows = stmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    private Product extractProductFromResultSet(ResultSet rs) throws SQLException {
        Product product = new Product();
        product.setId(rs.getInt("id"));
        product.setUserId(rs.getInt("user_id"));
        product.setName(rs.getString("name"));
        product.setDescription(rs.getString("description"));
        product.setPrice(rs.getBigDecimal("price"));
        product.setImageUrl(rs.getString("image_url"));
        product.setStatus(rs.getString("status"));
        product.setCreatedAt(rs.getTimestamp("created_at"));
        return product;
    }
} 